package com.situ.store.dao;

import java.util.ArrayList;
import java.util.List;

import com.situ.store.entity.Order;
import com.situ.store.util.DBCP;
import com.situ.store.util.StringUtil;
import com.situ.store.vo.DataVO;
import com.situ.store.vo.OrderTVO;
import com.situ.store.vo.OrderVO;

public class OrderDao {

	public void add(OrderVO o) {
		String sql = "INSERT INTO t_order(good_id, count, or_date, or_time, place, dp_name, op_name, state) VALUES(?,?,?,?,?,?,?,?)";
		DBCP.excuteDML(sql, o.getGoodId(), o.getCount(), StringUtil.parseStr(o.getOrDate()),
				o.getOrTime(), o.getPlace(), o.getDpName(), o.getOpName(), o.getState());
	}

	public List<OrderVO> selectAll(Integer page, Integer limit, String keyword, String date) {
		String sql = "SELECT o.*, g.`name`, g.factory, g.model, g.specs " 
				+ "FROM t_order AS o INNER JOIN t_goods AS g "
				+ "ON o.good_id = g.id " 
				+ "WHERE INSTR(name, '" + keyword + "') > 0 "
				+ "AND CONVERT(or_date, DATE) LIKE '" 
				+ date + "%' LIMIT ?, ?";
		return DBCP.excuteDQL(sql, OrderVO.class, page, limit);
	}
	

	public Long count(String keyword, String date) {
		String sql = "SELECT COUNT(1)" + "FROM t_order AS o INNER JOIN t_goods AS g " + "ON o.good_id = g.id "
				+ "WHERE INSTR(name, '" + keyword + "') > 0 " 
				+ "AND CONVERT(or_date, DATE) LIKE '" 
				+ date + "%'";
		return DBCP.executeCount(sql);
	}

	public void edit(OrderVO o) {
		//System.out.println(o);
		String sql = "UPDATE t_order SET good_id = ?, count = ?, or_date = ?, or_time = ?, place = ?, dp_name = ?, state = ?, op_name = ? WHERE id = ?";
		DBCP.excuteDML(sql, o.getGoodId(), o.getCount(), o.getOrDate(), o.getOrTime(), o.getPlace(), o.getDpName(), o.getState(), o.getOpName(), o.getId());
	}

	public void del(Integer id) {
		String sql = "DELETE FROM t_order WHERE id = ?";
		DBCP.excuteDML(sql, id);
	}
	
	public List<OrderVO> selectById(Integer id) {
		String sql = "SELECT * FROM t_order WHERE id = ?";
		return DBCP.excuteDQL(sql, OrderVO.class, id);
	}
	
	public List<Order> sum(String keyword) {
		List<Order> l = new ArrayList<>();
		String sqlIn = "SELECT CAST(SUM(count) AS DECIMAL(10,2)) AS count FROM t_order " 
				+ " WHERE state = '入库' "
				+ " AND CONVERT(or_date,DATE) LIKE '" + keyword + "%'";
		String sqlOut = "SELECT CAST(SUM(count) AS DECIMAL(10,2)) AS count FROM t_order " 
				+ " WHERE state = '出库' "
				+ " AND CONVERT(or_date,DATE) LIKE '"+ keyword + "%'";
		l.addAll(DBCP.excuteDQL(sqlIn,Order.class));
		l.addAll(DBCP.excuteDQL(sqlOut, Order.class));
		return l;
	}


	public List<DataVO> statisticsByYearIN(){
		String sql = "SELECT SUM(count) AS count, YEAR(or_date) AS `or_year` FROM t_order WHERE state = '入库'  GROUP BY `or_year` ORDER BY `or_year`";
		return DBCP.excuteDQL(sql, DataVO.class);
	}


	public List<DataVO>statisticsByYearOUT() {
		String sql = "SELECT SUM(count) AS count, YEAR(or_date) AS `or_year` FROM t_order WHERE state = '出库'  GROUP BY `or_year` ORDER BY `or_year`";
		return DBCP.excuteDQL(sql, DataVO.class);
	}

	public List<OrderTVO> statisticsByYearInfo(String byYear) {
		String yearB = StringUtil.regExpYear(byYear).get(0);
		String yearE = StringUtil.regExpYear(byYear).get(1);
		String sql = "SELECT t.`name`, SUM(t.count) AS count, t.state, t.or_year, t.state FROM " +
				"(SELECT g.`name`, CAST(o.count AS DECIMAL(10,2)) AS count, o.state, YEAR(o.or_date) AS or_year " +
				" FROM t_order AS o INNER JOIN t_goods AS g ON o.good_id = g.id ) AS t " +
				" WHERE or_year BETWEEN " + yearB + " AND "+ yearE +
				" GROUP BY t.`name`, t.or_year, t.state ORDER BY t.or_year, count";
		return DBCP.excuteDQL(sql, OrderTVO.class);
	}

	public List<OrderTVO> statisticsByMonthInfo(String byMonth){
		String monthB = StringUtil.regExpMonth(byMonth).get(0);
		String monthE = StringUtil.regExpMonth(byMonth).get(1);
		String sql = "SELECT t.`name`, SUM(t.count) AS count, t.or_month, t.state FROM " +
				"(SELECT g.`name`, CAST(o.count AS DECIMAL(10,2)) AS count, o.state, DATE_FORMAT(o.or_date, '%Y-%m') AS or_month " +
				" FROM t_order AS o INNER JOIN t_goods AS g ON o.good_id = g.id) AS t " +
				" WHERE or_month BETWEEN '" + monthB + "' AND '" + monthE + "'" +
				" GROUP BY t.`name`, t.or_month, t.state ORDER BY t.or_month, count";
		return DBCP.excuteDQL(sql, OrderTVO.class);
	}


	public List<OrderTVO> statisticsByDayInfo(String byDay) {
		String dayB = StringUtil.regExpDay(byDay).get(0);
		String dayE = StringUtil.regExpDay(byDay).get(1);
		String sql = "SELECT t.`name`, SUM(t.count) AS count, t.or_day, t.state FROM " +
				" (SELECT g.`name`, CAST(o.count AS DECIMAL(10,2)) AS count, o.state, DATE_FORMAT(o.or_date, '%Y-%m-%d') AS or_day " +
				" FROM t_order AS o INNER JOIN t_goods AS g ON o.good_id = g.id) AS t " +
				" WHERE or_day BETWEEN '" + dayB + "' AND '"+ dayE + "'" +
				" GROUP BY t.`name`, t.or_day, t.state ORDER BY t.or_day, count";
		return DBCP.excuteDQL(sql, OrderTVO.class);
	}
}
